from sqlalchemy.orm import Session
from sqlalchemy import func
from typing import List, Dict, Optional
from ..students.models import Student
from ..scores.models import Score
from ..employment.models import Employment
from .schemas import (
    ClassGenderStats, GenderCount, HighScoreStudent,
    FrequentFailStudent, FailedExamDetail, ClassExamAverageScore,
    TopSalaryStudent
)


def get_students_over_30(db: Session) -> List[Student]:
    # 获取年龄超过30岁的学生
    return db.query(Student).filter(Student.age > 30).all()


def get_class_gender_stats(db: Session) -> List[ClassGenderStats]:
    """统计每个班级男生女生的人数"""
    # 先获取所有班级名称
    classes = db.query(Student.class_name).distinct().all()
    
    results = []
    for class_name in classes:
        class_name = class_name[0] if isinstance(class_name, tuple) else class_name
        
        # 统计男生人数
        male_count = db.query(Student).filter(
            Student.class_name == class_name,
            Student.sex == '男'
        ).count()
        
        # 统计女生人数
        female_count = db.query(Student).filter(
            Student.class_name == class_name,
            Student.sex == '女'
        ).count()
        
        gender_count = GenderCount(male=male_count, female=female_count)
        results.append(ClassGenderStats(class_name=class_name, gender_count=gender_count))
    
    return results





def get_high_score_students(db: Session) -> List[HighScoreStudent]:
    """查询每次考试成绩都在80分以上的学生的编号，姓名和成绩"""
    # 首先获取所有学生ID
    all_students = db.query(Score.student_id, Score.student_name).distinct().all()
    results = []
    
    for student in all_students:
        student_id, student_name = student
        
        # 检查该学生是否有低于80分的成绩
        has_low_score = db.query(Score).filter(
            Score.student_id == student_id,
            Score.score < 80
        ).first()
        
        if not has_low_score:
            # 计算该学生的最低分
            min_score = db.query(func.min(Score.score)).filter(
                Score.student_id == student_id
            ).scalar()
            
            results.append(HighScoreStudent(
                student_id=student_id,
                student_name=student_name or '',  # 确保不是None
                min_score=min_score
            ))
    
    return results


def get_frequent_fail_students(db: Session) -> List[FrequentFailStudent]:
    """查询有两次以上不及格的学生的姓名，班级和不及格成绩"""
    # 先获取有两次以上不及格的学生ID
    fail_threshold = 60  # 假设60分为及格线
    
    # 分组统计每个学生的不及格次数
    fail_counts = db.query(
        Score.student_id,
        Score.student_name,
        Score.class_name,
        func.count().label('fail_count')
    ).filter(
        Score.score < fail_threshold
    ).group_by(
        Score.student_id,
        Score.student_name,
        Score.class_name
    ).having(
        func.count() >= 2
    ).all()
    
    results = []
    
    for student_info in fail_counts:
        student_id, student_name, class_name, _ = student_info
        
        # 获取该学生的所有不及格成绩
        failed_exams = db.query(Score).filter(
            Score.student_id == student_id,
            Score.score < fail_threshold
        ).all()
        
        # 构建不及格成绩详情列表
        failed_exam_details = [
            FailedExamDetail(
                exam_sequence=exam.exam_sequence,
                score=exam.score
            )
            for exam in failed_exams
        ]
        
        results.append(FrequentFailStudent(
            student_name=student_name or '',  # 确保不是None
            class_name=class_name or '',  # 确保不是None
            failed_exams=failed_exam_details
        ))
    
    return results


def get_class_average_scores(db: Session) -> List[ClassExamAverageScore]:
    """统计每次考试每个班级的平均分，按照从高到低排序"""
    # 分组统计每个考试每个班级的平均分
    class_averages = db.query(
        Score.class_name,
        Score.exam_sequence,
        func.avg(Score.score).label('average_score')
    ).group_by(
        Score.class_name,
        Score.exam_sequence
    ).order_by(
        func.avg(Score.score).desc()
    ).all()
    
    # 转换结果
    results = [
            ClassExamAverageScore(
                class_name=item.class_name or '',  # 确保不是None
                exam_sequence=item.exam_sequence,
                average_score=float(item.average_score) if item.average_score is not None else 0
            )
            for item in class_averages if item.class_name is not None  # 只包含有班级名称的记录
    ]
    
    return results


def get_top_salary_students(db: Session) -> List[TopSalaryStudent]:
    """统计就业薪资最高的前五名学生的姓名，班级和就业时间，就业公司"""
    # 查询薪资最高的前五名学生
    top_salary_records = db.query(Employment).filter(
        Employment.salary.isnot(None)
    ).order_by(
        Employment.salary.desc()
    ).limit(5).all()
    
    # 转换结果
    results = [
        TopSalaryStudent(
            student_name=record.student_name,
            class_name=record.class_name,
            offer_date=record.offer_date,
            company_name=record.company_name,
            salary=record.salary
        )
        for record in top_salary_records
    ]
    
    return results